import pymysql
import xlwt

# 连接到数据库
db = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='123456',
    database='33info_platform_shuyang'
)

# 要查询的数据库名和表名
search_db_name = '33info_platform_shuyang'
search_table_names = [
    'sys_announcement',
    'sys_announcement_auth',
    'sys_config',
    'sys_config_img',
    'sys_dept',
    'sys_dept_user',
    'sys_dictionary',
    'sys_districts',
    'sys_file',
    'sys_log',
    'sys_menu',
    'sys_menu_auth',
    'sys_notice',
    'sys_notice_acl',
    'sys_role',
    'sys_role_menu',
    'sys_tenant',
    'sys_tenant_menu',
    'sys_tenant_user',
    'sys_user',
    'sys_user_role'
]


#新建一个excel
book = xlwt.Workbook()
#添加一个sheet页
sheet = book.add_sheet('table_schema')

line = 0#控制行
 
#设置某列的单元格宽度
sheet.col(0).width = 4000
sheet.col(1).width = 4000
sheet.col(2).width = 8000
sheet.col(3).width = 4500
sheet.col(4).width = 3000
sheet.col(5).width = 2200
sheet.col(6).width = 2500
sheet.col(7).width = 2000
sheet.col(8).width = 10000


for search_table_name in search_table_names:

    sql =   "SELECT \
                TABLE_SCHEMA,\
                TABLE_NAME,\
                column_name,\
                column_comment,\
                data_type,\
                character_maximum_length,\
                is_nullable,\
                column_default,\
                column_type \
            FROM \
                `information_schema`.`columns` \
            WHERE \
                table_schema =" + "'" + search_db_name + "'" + \
            "AND \
                table_name=" + "'" + search_table_name + "'"
    
    # 输出数据表名
    title_style = xlwt.XFStyle()#初始化样式
    title_font = xlwt.Font()#创建字体
    title_font.name = u'微软雅黑' #字体类型
    title_font.height = 280    #字体大小   200等于excel字体大小中的10
    title_style.font = title_font   #设定样式
    sheet.write_merge(line,line,0,6,search_table_name,title_style)
    line+=1

    # 字段名的中文描述
    des_style = xlwt.XFStyle()#初始化样式
    des_font = xlwt.Font()#创建字体
    des_font.name = u'宋体' #字体类型
    des_font.height = 240    #字体大小   200等于excel字体大小中的10
    des_style.font = des_font   #设定样式
    description = ['数据库名称','表名','列名','备注','字段类型','长度','是否为空','默认值','数据类型']
    des_col = 0
    for des in description:
        sheet.write(line,des_col,des,des_style)
        des_col+=1
    line+=1

    try:
        cursor = db.cursor()
        cursor.execute(sql)
        results = cursor.fetchall()
        # 输出字段的信息
        content_style = xlwt.XFStyle()#初始化样式
        content_font = xlwt.Font()#创建字体
        content_font.name = u'微软雅黑' #字体类型
        content_font.height = 240    #字体大小   200等于excel字体大小中的10
        content_style.font = content_font   #设定样式
 
        for row in results:
            col = 0#控制列
            for s in row:#再循环里面list的值，每一列
                sheet.write(line,col,s,content_style)
                col+=1
            line+=1
        
        for i in [0,1,2,3,4,5,6]:
            sheet.write(line,i,'')
        line+=1
        
        for i in [0,1,2,3,4,5,6]:
            sheet.write(line,i,'')
        line+=1
 
    except Exception as e:
        print(e)
        print ("Error: unable to fetch data")

book.save('C:/Users/FF998/Desktop/Work/沭阳/综合主表(sys表)相关信息.xlsx')#保存到当前目录下
 
db.close()